package cn.pconline.autoclub.task;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.PreparedStatement;

/**
 * @author 作者 guoqiang
 * @version 2014-3-31 车友会人员统计定时任务
 */
public class ClubTotalMemberTask {

    private static Logger log = LoggerFactory.getLogger(ClubTotalMemberTask.class);

    public static void setLog(Logger log) {
        ClubTotalMemberTask.log = log;
    }
    private String hasNoRecordClub = "select club_id,member_num,vip_num from ac_club a where not exists (select 1 from ac_club_total_member b where a.club_id = b.club_id)";
    private String hasRecordClub = "select club_id,member_num,vip_num from ac_club a where exists (select 1 from ac_club_total_member b where a.club_id = b.club_id)";
    private String insertNewRecord = " INSERT INTO ac_club_total_member(club_id,lastweek_total,week_total,lastweek_vip_total,week_vip_total,update_at) VALUES (?,?,?,?,?,NOW()) ";
    private String updateTotal2lastWeek = " UPDATE ac_club_total_member SET lastweek_total = week_total,lastweek_vip_total = week_vip_total WHERE club_id IN (SELECT club_id FROM ac_club) ";
    private String updateRecord = " UPDATE ac_club_total_member SET week_total = ?,week_vip_total = ?,update_at = NOW() WHERE club_id = ? ";

    /**
     * 汇总车友会会员人数表任务
     * 记录本周和上周：会员人数和认证人数
     *
     * @throws SQLException
     */
    public void updateTotalMember() throws SQLException {

//        if (DateUtils.getTodayIndex() != 0) {
//            //如果今天不是周末，则结束
//            log.error("today is not sunday!!!");
//            return;
//        }
        Connection conn = DBUtils.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement ps;
        ResultSet rs;
        
        List<Club> clubList1 = new ArrayList<Club>(1024);
        //取出统计表中有数据的车友会，直接更新
        ps = conn.prepareStatement(hasRecordClub);
        rs = ps.executeQuery();
        while (rs.next()) {
            Club c = new Club();
            c.setClubId(rs.getLong("club_id"));
            c.setMemberNum(rs.getInt("member_num"));
            c.setVipNum(rs.getInt("vip_num"));
            clubList1.add(c);
        }
        ps = conn.prepareStatement(updateTotal2lastWeek);
        ps.executeUpdate();

        for (Club c : clubList1) {
            ps = conn.prepareStatement(updateRecord);
            ps.setLong(1, c.getMemberNum());
            ps.setLong(2, c.getVipNum());
            ps.setLong(3, c.getClubId());
            ps.executeUpdate();
        }
        
        List<Club> clubList = new ArrayList<Club>(1024);
        //取出统计表中没有数据的车友会，直接插入新值
        ps = conn.prepareStatement(hasNoRecordClub);
        rs = ps.executeQuery();
        while (rs.next()) {
            Club c = new Club();
            c.setClubId(rs.getLong("club_id"));
            c.setMemberNum(rs.getInt("member_num"));
            c.setVipNum(rs.getInt("vip_num"));
            clubList.add(c);
        }

        for (Club c : clubList) {
            ps = conn.prepareStatement(insertNewRecord);
            ps.setLong(1, c.getClubId());
            ps.setInt(2, 0);
            ps.setLong(3, c.getMemberNum());
            ps.setLong(4, 0);
            ps.setLong(5, c.getVipNum());
            ps.executeUpdate();
        }
        
        conn.commit();
        conn.setAutoCommit(true);
        DBUtils.releaseConn(conn, ps, rs);
    }
}
